import java.sql.*;
import java.util.Scanner;

//TIP 要<b>运行</b>代码，请按 <shortcut actionId="Run"/> 或
// 点击装订区域中的 <icon src="AllIcons.Actions.Execute"/> 图标。
public class Main {
    public static void main(String[] args) {
        //查询
        m1();

        //条件查询
        m2();

        //增加
        m3();

        //删除
        m4();

        //修改
        m5();


//
   }
    private static void m1() {
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接

            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection connection =DriverManager.getConnection(url, "root", "200522");
                    if(connection !=null){
                        System.out.println("连接成功");
                    }
            //3.写sql
            String sql="select *from diyi";
//            4.预编译sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //5.有参数设置参数
            //6.执行sql
            ResultSet resultSet= preparedStatement.executeQuery();
            //7.遍历结果集
            while(resultSet.next()){
                int id=resultSet.getInt(1);
                String name=resultSet.getString(2);
                String pass=resultSet.getString(3);
                System.out.println(id+"...."+name+"....."+pass);
            }
            //8.关闭资源
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static void m2() {
        //1.加载驱动
        try {
            Scanner sc=new Scanner(System.in);
            System.out.println("请输入查询的名子");
            String word=sc.next();
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接

            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection connection =DriverManager.getConnection(url, "root",
                    "200522");
//                    if(connection !=null){
//                        System.out.println("连接成功");
//                    }
            //3.写sql
            String sql="select *from diyi where name like?";
//            4.预编译sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //5.有参数设置参数
            preparedStatement.setObject(1,"%"+word+"%");
            //6.执行sql
            ResultSet resultSet= preparedStatement.executeQuery();
            //7.遍历结果集
            while(resultSet.next()){
                int id=resultSet.getInt(1);
                String name=resultSet.getNString(2);
                String pass=resultSet.getNString(3);
                System.out.println(id+"...."+name+"....."+pass);
            }
            //8.关闭资源
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    private static void m3() {
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            System.out.println("请输入你的姓名和密码");
            Scanner scanner=new Scanner(System.in);
            String name=scanner.next();
            String passward=scanner.next();
            String sql="insert into user(name,pass) values (?,?)";
            PreparedStatement de=connection.prepareStatement(sql);
            de.setString(1,name);
            de.setString(2,passward);
            de.executeUpdate();
            System.out.println("已成功添加您的账户");
            connection.close();
            de.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static void m4(){//删除
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            System.out.println("请输入您想删除的用户名");
            Scanner scanner=new Scanner(System.in);
            String name=scanner.next();
            String sql="delete from user where name=?";
            PreparedStatement de=connection.prepareStatement(sql);
            de.setString(1,name);
            de.executeUpdate();
            System.out.println("已成功删除");
            connection.close();
            de.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static void m5(){//修改

        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            Scanner scanner=new Scanner(System.in);
            System.out.println("请输入您想修改的用户的用户名");
            String lname=scanner.nextLine();
            System.out.println("请输入新的用户名和密码");
            String xname=scanner.next();
            String mima=scanner.next();
            String sql="select id from user where name=?";
            PreparedStatement de=connection.prepareStatement(sql);
            de.setString(1,lname);
            ResultSet ft=de.executeQuery();
            int h= chaid(connection,lname);
            String xsql="update user set name=?,pass=? where id=?";
            PreparedStatement gh=connection.prepareStatement(xsql);
            gh.setString(1,xname);
            gh.setString(2,mima);
            gh.setInt(3,h);
            gh.executeUpdate();
            System.out.println("修改成功");
            connection.close();
            de.close();
            ft.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static int chaid(Connection con,String name){
        String dr="Select id from user where name=?";
        try {PreparedStatement po=con.prepareStatement(dr);
            po.setString(1,name);
            ResultSet rt=po.executeQuery();
            if(rt.next()){
                return rt.getInt("id");
            }

        }catch (SQLException e){
            System.out.println("查找用户id出现错误"+e);
        }
        return -1;
    }

}